91

Merging Sheets

91

STORE

TYPE

COLOR

SIZE

NUMBER SOLD

Second Street

Wonderland

red

S

100

Second Street

Sandy

blue

M

100

Second Street

Sandy

green

L

200

Second Street

Sandy

black

S

300

Second Street

Sandy

navy

M

100

Third Street

Wonderland

red

S

100

Third Street

Sandy

blue

M

100

Third Street

Sandy

green

L

200

Third Street

Sandy

black

S

300

Third Street

Sandy

navy

M

100

:

:

:

100th Street

Sandy

black

S

350

Now this is quite a bit of work.

Imagine if the survey contained two sheets in the same book. Perhaps sheet 1 is

shoes and sheet 2 is dresses.

The work now doubles.

What about more than two sheets?

You get the picture. This needs some sort of automation.

6.1  ALGORITHM FOR MERGING SHEETS

In the algorithmic world, the steps are very similar, but we can use simple loops to do

what we are doing manually.

This would translate to a set of steps as below:

Step 1: Open a new spreadsheet

Step 2: Create the header row in every worksheet

Step 3: For each workbook from a store

For each worksheet in the workbook

Read rows 2 to last row that contains data

Move this row to the current row

Advance to next row

Next worksheet

Next workbook

Step 4: Save workbook

6.2  USER INTERFACE

Using this algorithm, we can reduce the steps significantly and we can use these steps

to now write the program that can automate it.

Following is the code that can do this task.

First create a spreadsheet in which you will write the code.

Create the entries exactly as shown below.